#include "operatedb.h"
#include <QDebug>
#include <QSqlError>
#include <QSqlQuery>

OperateDB &OperateDB::getInstance()
{
    static OperateDB instance;
    return instance;
}

OperateDB::OperateDB(QObject *parent) : QObject(parent)
{
    m_db = QSqlDatabase::addDatabase("QMYSQL");
}

// 连接数据库
void OperateDB::connect()
{
    m_db.setHostName    ("localhost");
    m_db.setPort        (3306       );
    m_db.setUserName    ("root"     );
    m_db.setPassword    ("123456"   );
    m_db.setDatabaseName("ydj"      );
    if(m_db.open()){
        qDebug() << "数据库连接成功";
    } else{
        qDebug() << "数据库连接失败" << m_db.lastError().text();
    }
}

OperateDB::~OperateDB()
{
    m_db.close();
}

// 数据库处理注册请求
bool OperateDB::handleResgist(const char *name, const char *pwd)
{
    if(name == NULL || pwd == NULL)
    {
        return false;
    }
    //判断用户是否存在
    QString sql = QString("select * from user_info where name = '%1'").arg(name);
    qDebug() << "数据库-判断注册用户是否已存在->";
    QSqlQuery q;
    if(!q.exec(sql) || q.next())
    {
        return false;
    }
    //创建用户
    sql = QString("insert into user_info(name, pwd) value('%1', '%2')").arg(name).arg(pwd);
    qDebug() << "数据库-注册用户->";
    return q.exec(sql);
}

bool OperateDB::handleLogin(const char *name, const char *pwd)
{
    if(name == NULL || pwd == NULL)
    {
        return false;
    }
    QString sql = QString("select * from user_info where name = '%1' and pwd = '%2'").arg(name).arg(pwd);
    QSqlQuery q;
    qDebug() << "数据库-查询登录用户是否存在";
     if(!(q.exec(sql) && q.next()))
    {
        return false;
    }
    sql = QString("update user_info set online = 1 where name = '%1' and pwd = '%2'").arg(name).arg(pwd);
    qDebug() << "数据库-更新该用户的在线情况->上线";
    return q.exec(sql);
}

void OperateDB::handleOffline(const char *name)
{
    QString sql = QString("update user_info set online = 0 where name = '%1'").arg(name);
    qDebug() << "数据库-更新该用户的在线情况->下线";
    QSqlQuery q;
    q.exec(sql);
}

int OperateDB::handleFindUser(const char *name)
{
    QString sql = QString("select online from user_info where name = '%1'").arg(name);
    qDebug() << "数据库-查找该用户:" << sql;
    QSqlQuery q;
    q.exec(sql);
    if(q.next()){
        return q.value(0).toInt(); // 返回0代表离线，1代表在线
    }
    return -1; // -1代表不存在

}

QStringList OperateDB::handleOnlineUser()
{
    QString sql = QString("select name from user_info where online = 1");
    qDebug() << "数据库-处理在线用户:" << sql;
    QSqlQuery q;
    q.exec(sql);
    QStringList res;
    while(q.next())
    {
        res.append(q.value(0).toString());
    }
    return res; // -1代表不存在
}

int OperateDB::handleAddFriend(const char *caCurName, const char *caTarName)
{
    if(caCurName == NULL || caTarName == NULL) return -1;
    QString sql = QString(R"(
                          select * from friend
                          where
                            (
                              user_id = (select id from user_info where name = '%1')
                              and
                              friend_id = (select id from user_info where name = '%2')
                            )
                            or
                            (
                              friend_id = (select id from user_info where name = '%1')
                              and
                              user_id = (select id from user_info where name = '%2')
                            )
                          )").arg(caCurName).arg(caTarName);
    qDebug() << "数据库-验证双方是否已是好友->";
    qDebug() << sql;
    QSqlQuery q;
    if(!(q.exec(sql))) qDebug() << sql;

    if(q.next()) return -2; // 已经是好友
    sql = QString("select online from user_info where name = '%1'").arg(caTarName);
    qDebug() << "数据库-验证应答方是否已在线->";
    q.exec(sql);
    if(q.next()){
        return q.value(0).toInt();
    }else{
        return -1;
    }
}

void OperateDB::handleAddFriendAgree(const char *caCurName, const char *caTarName)
{
    if(caCurName == NULL || caTarName == NULL) return;
    QString sql = QString(R"(
                          insert into friend(user_id, friend_id)
                            select u1.id, u2.id
                            from user_info u1, user_info u2
                            where u1.name = '%1' and u2.name = '%2'
                          )").arg(caCurName).arg(caTarName);
    QSqlQuery q;
    q.exec(sql);
}

QStringList OperateDB::handleFlushFriend(const char *name)
{
    QString sql = QString(R"(
                          select name from user_info
                          where id in
                          (
                          select user_id from friend where friend_id = (select id from user_info where name = '%1')
                          union
                          select friend_id from friend where user_id = (select id from user_info where name = '%1')
                          ) and online = 1
                          )").arg(name);
    qDebug() << "数据库-处理刷新好友:";
    QSqlQuery q;
    q.exec(sql);
    QStringList res;
    while(q.next())
    {
        res.append(q.value(0).toString());
    }
    return res; // -1代表不存在
}

int OperateDB::handleDelFriend(const char *caCurName, const char *caTarName)
{
    if(caCurName == NULL || caTarName == NULL) return 0;
    QString sql = QString(R"(
                          select * from friend
                          where
                          (
                            user_id = (select id from user_info where name = '%1')
                            and
                            friend_id = (select id from user_info where name = '%2')
                          )
                          or
                          (
                            friend_id = (select id from user_info where name = '%1')
                            and
                            user_id = (select id from user_info where name = '%2')
                          )
                          )").arg(caCurName).arg(caTarName);
    qDebug() << "数据库-验证双方是否已是好友->";
    QSqlQuery q;
    if(!(q.exec(sql))) qDebug() << sql;
    if(!(q.next())) return -1; // 不是好友
    sql = QString(R"(
                     delete from friend
                     where
                     (
                       user_id = (select id from user_info where name = '%1')
                       and
                       friend_id = (select id from user_info where name = '%2')
                     )
                     or
                     (
                       friend_id = (select id from user_info where name = '%1')
                       and
                       user_id = (select id from user_info where name = '%2')
                      )
                      )").arg(caCurName).arg(caTarName);
    qDebug() << "数据库-删除双方好友关系->";
    return q.exec(sql);
}


